package com.budwk.app.util.poiUtils;

import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.StrUtil;
import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.budwk.app.sqfw.dto.FireHiddenExcelParamWeiQingMenSQDTO;
import com.budwk.app.sqfw.enums.DownLoadFileNameEnum;
import com.budwk.app.sqfw.enums.WeiQingMenExcelType;
import com.budwk.app.sqfw.models.Sqfw_cenSusPaper;
import com.budwk.app.sqfw.models.Sqfw_formSummary;
import com.budwk.app.util.poiUtils.excelPictureConfigBO.ExcelFileCreateParamBO;
import com.budwk.app.util.poiUtils.excelPictureConfigBO.FireHiddenExcelWeiQingMenBO;
import com.google.common.collect.Lists;
import com.google.common.collect.Maps;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;

import java.util.*;
import java.util.stream.Collectors;

/**
 * 威清门excel
 *
 * @author yangweimin
 * @since 2023/10/10
 */
public class WeiQingMenStreetFireHiddenExcelUtils {

    private static Map<String, Object> map = new HashMap<>();

    static {
        map.put("无", "");
        map.put("经排查暂无隐患", "");
        map.put("暂未发现隐患", "");
        map.put("未发现安全隐患", "");
        map.put("暂未发现问题", "");
        map.put("暂无发现问题", "");
        map.put("经排查暂无发现隐患", "");
        map.put("只配备灭火器暂未发现安全隐患", "");
        map.put("经排查暂无隐患该经营户使用诚纳源和醇基燃料", "");
        map.put("经排查电线穿管配备灭火器暂无隐患", "");
    }

    /**
     * 威清门街道消防安全excel
     *
     * @param excelParamDTO excel参数
     * @return excel链接
     */
    public static String createFireHiddenExcelSQ(FireHiddenExcelParamWeiQingMenSQDTO excelParamDTO, String isCheckData) {
        String unitName = excelParamDTO.getCompleteUnitName();
        String userName = excelParamDTO.getCreateUserName();
        String phone = excelParamDTO.getCreateUserPhone();
        int fileIndex = excelParamDTO.getFileIndex();
        Map<String, Object> threeDetailedParamMap = Maps.newHashMap();
        threeDetailedParamMap.put("province", excelParamDTO.getProvince());
        threeDetailedParamMap.put("city", excelParamDTO.getCity());
        threeDetailedParamMap.put("district", excelParamDTO.getDistrict());
        threeDetailedParamMap.put("county", excelParamDTO.getCounty());
        threeDetailedParamMap.put("community", excelParamDTO.getCommunity());
        List<Sqfw_formSummary> formSummaryList = excelParamDTO.getFormSummaryList();
        // zhangp 2023-11-27 下载模板放行
        /*if (formSummaryList == null || formSummaryList.size() == 0) {
            return null;
        }*/

        // 九小场所日常排查台账
        if (fileIndex == 1) {
            String chargeUser = null;
            if (unitName.contains("下威清社区")) chargeUser = "陈定美";
            if (unitName.contains("威西门社区")) chargeUser = "贺湘林";
            if (unitName.contains("瑞北社区")) chargeUser = "郭琼";
            if (unitName.contains("延安社区")) chargeUser = "皱邓铁";
            if (unitName.contains("鲤鱼社区")) chargeUser = "周李萍";

            if (excelParamDTO.getIsTemplate()) {
                threeDetailedParamMap.put("downloadDate", null);
                threeDetailedParamMap.put("chargeUser", null);
                threeDetailedParamMap.put("userName", null);
            } else {
                threeDetailedParamMap.put("downloadDate", DateUtil.format(new Date(), "yyyy年MM月dd日"));
                threeDetailedParamMap.put("chargeUser", chargeUser);
                threeDetailedParamMap.put("userName", userName);
            }

            List<Map<String, Object>> dataList = Lists.newArrayList();

            int num = 0;
            int total_1 = 0;
            for (Sqfw_formSummary formSummary : formSummaryList) {
                //zhangp 2023-12-13 报空 判断空
                if (null != formSummary.getA61() && "餐饮店面/店面/临摊、夜市摊".contains(formSummary.getA61())) {

                    //zhangp 2023-12-25 只要检测到有一条数据就返回
                    if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                        return "true";
                    }

                    num++;
                    Map<String, Object> rollDataMap = Maps.newHashMap();
                    rollDataMap.put("num", num);
                    rollDataMap.put("a1", formSummary.getA1());
                    rollDataMap.put("a2", formSummary.getA2());
                    rollDataMap.put("a3", formSummary.getA3());
                    rollDataMap.put("a4", formSummary.getA4());
                    rollDataMap.put("a5", formSummary.getA5());
                    rollDataMap.put("a6", formSummary.getA6());
                    rollDataMap.put("a7", formSummary.getA7());
                    rollDataMap.put("a8", formSummary.getA8());
                    rollDataMap.put("a16", formSummary.getA16());
                    rollDataMap.put("a17", formSummary.getA17());
                    rollDataMap.put("a9", formSummary.getA9());
                    rollDataMap.put("a19", formSummary.getA19());
                    String a38 = getA38_A31(formSummary, Arrays.asList(0, 1, 2, 3, 4, 5));
                    rollDataMap.put("a38", a38);
                    if (!a38.equals("无")) {
                        total_1++;
                    }

                    dataList.add(rollDataMap);
                }
            }
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                if (dataList.isEmpty()) {
                    return "false";
                }
            }
            threeDetailedParamMap.put("total", num);
            threeDetailedParamMap.put("total_1", total_1);

            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("weiQingMen/nineSmallSitesDailyChecks.xlsx");
            //String fileName = "九小场所日常排查台账";
            String fileName = DownLoadFileNameEnum.WQL_NINE_SMALL_DAILY_INSPECTION.getName();
            if (StringUtils.isNotBlank(excelParamDTO.getFileNameSuffix()))
                fileName += excelParamDTO.getFileNameSuffix();

            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(threeDetailedParamMap);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(String.class);
            excelFileCreateParam.setFileName(fileName);
            excelFileCreateParam.setReturnWebUrlFlag(excelParamDTO.getReturnWebUrlFlag());
            excelFileCreateParam.setListNotLastRow(true);

            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        }

        // 消防安全重大风险隐患专项排查整治“三清单”
        if (fileIndex == 2) {
            List<Map<String, Object>> dataList = Lists.newArrayList();
            int num = 0;
            for (Sqfw_formSummary formSummary : formSummaryList) {
                //String a38 = getA38(formSummary, Arrays.asList(1, 2, 4, 5));
                // 12-15日改动,a31是其他隐患
                String a38 = getA38_A31(formSummary, Arrays.asList(0, 1, 2, 3, 4, 5));
                String a31 = formSummary.getA31();
                a31 = a31 == null ? "" : a31.replaceAll("[^0-9a-zA-Z\u4e00-\u9fa5]+","");
                if (formSummary.getA61() != null && "餐饮店面/店面/临摊、夜市摊".contains(formSummary.getA61()) &&
                        !"无".equals(a38) && !map.containsKey(a31)) {

                    //zhangp 2023-12-25 只要检测到有一条数据就返回
                    if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                        return "true";
                    }

                    num++;
                    Map<String, Object> rollDataMap = Maps.newHashMap();
                    rollDataMap.put("num", num);
                    rollDataMap.put("a1", formSummary.getA1());
                    rollDataMap.put("a2", formSummary.getA2());
                    rollDataMap.put("a3", formSummary.getA3());
                    rollDataMap.put("a4", formSummary.getA4());
                    rollDataMap.put("a6", formSummary.getA6());
                    String a39 = "";
                    String a41 = formSummary.getA41();
                    String a42 = formSummary.getA42();
                    JSONArray a41_2 = JSON.parseArray(a41).getJSONArray(1);
                    JSONArray a41_3 = JSON.parseArray(a41).getJSONArray(2);
                    JSONArray a42_2 = JSON.parseArray(a42).getJSONArray(1);
                    JSONArray a42_3 = JSON.parseArray(a42).getJSONArray(2);
                    if ((a41_2.size() + a41_3.size() > 0 && a42_2.size() + a42_3.size() > 0)
                            || a42_2.size() + a42_3.size() > 0) {
                        a39 = "督促在规定时间内整改";
                    } else if (a41_2.size() + a41_3.size() > 0) {
                        a39 = "现场督促整改";
                    } else {
                        a39 = "现场督促整改";
                    }
                    rollDataMap.put("a39", a39);
                    rollDataMap.put("a38", a38);
                    rollDataMap.put("a42", getA42(formSummaryList, formSummary, Arrays.asList(1, 2)));
                    dataList.add(rollDataMap);
                }
            }
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                if (dataList.isEmpty()) {
                    return "false";
                }
            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("weiQingMen/threeListsOfMajorRisksAndHiddenDangers.xlsx");
            //String fileName = "消防安全重大风险隐患专项排查整治“三清单”";
            String fileName = DownLoadFileNameEnum.WQL_FIRE_SAFETY_INSPECTION.getName();

            if (StringUtils.isNotBlank(excelParamDTO.getFileNameSuffix()))
                fileName += excelParamDTO.getFileNameSuffix();

            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(threeDetailedParamMap);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(String.class);
            excelFileCreateParam.setFileName(fileName);
            excelFileCreateParam.setReturnWebUrlFlag(excelParamDTO.getReturnWebUrlFlag());

            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        }

        // 今冬明春隐患整改照片
        if (fileIndex == 12) {
            List<Map<String, Object>> dataList = Lists.newArrayList();
            int num = 0;
            for (Sqfw_formSummary formSummary : formSummaryList) {
                String a43 = formSummary.getA43();
                String a43_1 = null;
                String a43_2 = null;
                if (!StrUtil.isBlank(a43)) {
                    if (a43.contains("[")) {
                        JSONArray jsonArray = JSON.parseArray(a43);
                        a43_1 = jsonArray.getString(0);
                        a43_2 = jsonArray.getString(1);
                    } else {
                        String urls[] = a43.split(",");
                        a43_1 = urls[0];
                        a43_2 = urls.length > 1 ? urls[1] : null;
                    }
                }
                String a38 = getA38_A31(formSummary, Arrays.asList(0, 1, 2, 3, 4, 5));
                if (formSummary.getA61() != null && "餐饮店面/店面/临摊、夜市摊".contains(formSummary.getA61()) && !"无".equals(a38) &&
                        (!StrUtil.isBlank(a43_1) || !StrUtil.isBlank(a43_2))) {

                    //zhangp 2023-12-25 只要检测到有一条数据就返回
                    if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                        return "true";
                    }

                    num++;
                    Map<String, Object> rollDataMap = Maps.newHashMap();
                    rollDataMap.put("num", num);
                    rollDataMap.put("a1", formSummary.getA1());

                    //图片处理
                    List<byte[]> imgBytes1 = PictureUtils.scaleImageToByte(a43_1, 4, false);
                    rollDataMap.put("a43_1", imgBytes1);
                    //图片处理
                    List<byte[]> imgBytes2 = PictureUtils.scaleImageToByte(a43_2, 4, false);
                    rollDataMap.put("a43_2", imgBytes2);

                    dataList.add(rollDataMap);
                }
            }

            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                if (dataList.isEmpty()) {
                    return "false";
                }
            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("weiQingMen/hiddenDangerRectificationPhotos.xlsx");
            String fileName = DownLoadFileNameEnum.WQL_FIRE_SAFETY_IMG.getName();

            if (StringUtils.isNotBlank(excelParamDTO.getFileNameSuffix()))
                fileName += excelParamDTO.getFileNameSuffix();

            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(threeDetailedParamMap);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(FireHiddenExcelWeiQingMenBO.class);
            excelFileCreateParam.setFileName(fileName);
            excelFileCreateParam.setReturnWebUrlFlag(excelParamDTO.getReturnWebUrlFlag());

            return CommonFormOfficeUtils.createWQMExcelAndUpload(excelFileCreateParam);
        }

        // 云岩区消防安全重点风险隐患专项排查整治“三清单”
        if (fileIndex == WeiQingMenExcelType.THREE_LISTS_OF_SPECIAL_PROJECT.getCode()) {
            List<Map<String, Object>> dataList = Lists.newArrayList();
            int num = 0;
            for (Sqfw_formSummary formSummary : formSummaryList) {
                //String a38 = getA38(formSummary, Arrays.asList(1, 2, 4, 5));
                // 12-15日改动,a31是其他隐患
                String a38 = getA38_A31(formSummary, Arrays.asList(0, 1, 2, 3, 4, 5));
                String a31 = formSummary.getA31();
                a31 = a31 == null ? "" : a31.replaceAll("[^0-9a-zA-Z\u4e00-\u9fa5]+","");
                if (formSummary.getA61() != null && "餐饮店面/店面/临摊、夜市摊".contains(formSummary.getA61()) &&
                        !"无".equals(a38) && !map.containsKey(a31)) {

                    //zhangp 2023-12-25 只要检测到有一条数据就返回
                    if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                        return "true";
                    }

                    String a43 = formSummary.getA43();
                    String a43_1 = null;
                    String a43_2 = null;
                    if (!StrUtil.isBlank(a43)) {
                        if (a43.contains("[")) {
                            JSONArray jsonArray = JSON.parseArray(a43);
                            a43_1 = jsonArray.getString(0);
                            a43_2 = jsonArray.getString(1);
                        } else {
                            String urls[] = a43.split(",");
                            a43_1 = urls[0];
                            a43_2 = urls.length > 1 ? urls[1] : null;
                        }
                    }

                    num++;
                    Map<String, Object> rollDataMap = Maps.newHashMap();
                    rollDataMap.put("num", num);
                    rollDataMap.put("a1", formSummary.getA1());
                    rollDataMap.put("a2", formSummary.getA2());
                    rollDataMap.put("a3", formSummary.getA3());
                    rollDataMap.put("a4", formSummary.getA4());
                    rollDataMap.put("a6", formSummary.getA6());
                    String a39 = "";
                    String a41 = formSummary.getA41();
                    String a42 = formSummary.getA42();
                    JSONArray a41_2 = JSON.parseArray(a41).getJSONArray(1);
                    JSONArray a41_3 = JSON.parseArray(a41).getJSONArray(2);
                    JSONArray a42_2 = JSON.parseArray(a42).getJSONArray(1);
                    JSONArray a42_3 = JSON.parseArray(a42).getJSONArray(2);
                    if ((a41_2.size() + a41_3.size() > 0 && a42_2.size() + a42_3.size() > 0)
                            || a42_2.size() + a42_3.size() > 0) {
                        a39 = "督促在规定时间内整改";
                    } else if (a41_2.size() + a41_3.size() > 0) {
                        a39 = "现场督促整改";
                    } else {
                        a39 = "现场督促整改";
                    }
                    rollDataMap.put("a39", a39);
                    rollDataMap.put("a38", a38);
                    rollDataMap.put("a42", getA42(formSummaryList, formSummary, Arrays.asList(1, 2)));

                    //图片处理
                    List<byte[]> imgBytes1 = PictureUtils.scaleImageToByte(a43_1, 1, false);
                    rollDataMap.put("beforeImg", imgBytes1);
                    //图片处理
                    List<byte[]> imgBytes2 = PictureUtils.scaleImageToByte(a43_2, 1, false);
                    rollDataMap.put("afterImg", imgBytes2);

                    dataList.add(rollDataMap);
                }
            }
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                if (dataList.isEmpty()) {
                    return "false";
                }
            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("weiQingMen/threeListsOfSpecialProject.xlsx");
            //String fileName = "消防安全重大风险隐患专项排查整治“三清单”";
            String fileName = WeiQingMenExcelType.THREE_LISTS_OF_SPECIAL_PROJECT.getName();

            if (StringUtils.isNotBlank(excelParamDTO.getFileNameSuffix()))
                fileName += excelParamDTO.getFileNameSuffix();

            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(threeDetailedParamMap);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(FireHiddenExcelWeiQingMenBO.class);
            excelFileCreateParam.setFileName(fileName);
            excelFileCreateParam.setReturnWebUrlFlag(excelParamDTO.getReturnWebUrlFlag());

            return CommonFormOfficeUtils.createWQMExcelAndUpload(excelFileCreateParam);
        }

        // 燃气安全排查情况统计周报表
        if (fileIndex == 3) {
            if (excelParamDTO.getIsTemplate()) {
                threeDetailedParamMap.put("unitName", null);
                threeDetailedParamMap.put("fillUserNamePhone", null);
                threeDetailedParamMap.put("downloadDate", null);
            } else {
                threeDetailedParamMap.put("unitName", unitName);
                threeDetailedParamMap.put("fillUserNamePhone", userName + "-" + phone);
                threeDetailedParamMap.put("downloadDate", DateUtil.format(new Date(), "yyyy年MM月dd日"));
            }

            List<Map<String, Object>> dataList = Lists.newArrayList();
            String county = null;
            int a44 = 0;
            int total = 0;
            int a38_2 = 0;
            int a38_3 = 0;
            int a49 = 0;
            int a50 = 0;
            int a38_2_d = 0;
            Map<String, Object> rollDataMap = Maps.newHashMap();
            for (Sqfw_formSummary formSummary : formSummaryList) {
                if (formSummary.getA61() != null && !formSummary.getA61().contains("重点") && !formSummary.getA61().contains("危房") &&
                        !formSummary.getA61().contains("工地")) {
                    if (StrUtil.isBlank(county)) {
                        county = formSummary.getCounty();
                    }
                    JSONArray a38 = JSON.parseArray(formSummary.getA38());
                    a44 += Integer.parseInt(formSummary.getA44());
                    total++;
                    a38_2 += a38.getJSONArray(1).size();
                    a38_3 += a38.getJSONArray(2).size();
                    if (a38.size() > 3) {
                        a38_2 += a38.getJSONArray(4).size();
                        a38_3 += a38.getJSONArray(5).size();
                    }
                    if (DateUtil.format(formSummary.getFillDate(), "yyyy年MM月dd日").equals(
                            DateUtil.format(new Date(), "yyyy年MM月dd日"))) {
                        a38_2_d += a38.getJSONArray(1).size();
                        if (a38.size() > 3) {
                            a38_2_d += a38.getJSONArray(4).size();
                        }
                    }
                    int changeNums[] = getChange(formSummaryList, formSummary);
                    JSONArray a41 = JSON.parseArray(formSummary.getA41());
                    int a41_1 = a41.getJSONArray(1).size();
                    int a41_2 = a41.getJSONArray(2).size();
                    int changeNum1 = changeNums[0] + a41_1;
                    int changeNum2 = changeNums[1] + a41_2;
                    a49 += changeNum1;
                    a50 += changeNum2;
                }
            }
            //zhangp 2023-12-06 下载模板时使用
            if (StringUtils.isBlank(county)) {
                county = unitName;
            }

            if (excelParamDTO.getIsTemplate()) {
                rollDataMap.put("num", null);
                rollDataMap.put("county", null);
                rollDataMap.put("a44", null);
                rollDataMap.put("total", null);

                // 当天隐患
                rollDataMap.put("a38_2_3_d", null);
                rollDataMap.put("a38_2_d", null);
                rollDataMap.put("a38_3_d", null);

                // 累计隐患
                //rollDataMap.put("a38_2_3", a38_2 + a38_3);
                rollDataMap.put("a38_2_3", null);
                rollDataMap.put("a38_2", null);
                //rollDataMap.put("a38_3", a38_3);
                rollDataMap.put("a38_3", null);
                rollDataMap.put("a49", null);
                //rollDataMap.put("a50", a50);
                rollDataMap.put("a50", null);
            } else {
                rollDataMap.put("num", total == 0 ? 0 : 1);
                rollDataMap.put("county", county);
                rollDataMap.put("a44", a44);
                rollDataMap.put("total", total);

                // 当天隐患
                rollDataMap.put("a38_2_3_d", a38_2_d + 0);
                rollDataMap.put("a38_2_d", a38_2_d);
                rollDataMap.put("a38_3_d", 0);

                // 累计隐患
                //rollDataMap.put("a38_2_3", a38_2 + a38_3);
                rollDataMap.put("a38_2_3", a38_2 + 0);
                rollDataMap.put("a38_2", a38_2);
                //rollDataMap.put("a38_3", a38_3);
                rollDataMap.put("a38_3", 0);
                rollDataMap.put("a49", a49);
                //rollDataMap.put("a50", a50);
                rollDataMap.put("a50", 0);
            }

            dataList.add(rollDataMap);
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                if (!dataList.isEmpty() && !formSummaryList.isEmpty()) {
                    return "true";
                } else {
                    return "false";
                }

            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("weiQingMen/gasSafetyCheck.xlsx");
            //String fileName = "燃气安全排查情况统计周报表";
            String fileName = DownLoadFileNameEnum.WQL_GAS_SAFETY_INSPECTION.getName();

            if (StringUtils.isNotBlank(excelParamDTO.getFileNameSuffix()))
                fileName += excelParamDTO.getFileNameSuffix();

            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(threeDetailedParamMap);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(String.class);
            excelFileCreateParam.setFileName(fileName);
            excelFileCreateParam.setReturnWebUrlFlag(excelParamDTO.getReturnWebUrlFlag());

            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        }

        // 夜市摊点、游摊游贩燃气使用、存储情况台账表
        if (fileIndex == 4) {

            if (excelParamDTO.getIsTemplate()) {
                threeDetailedParamMap.put("unitName", null);
                threeDetailedParamMap.put("fillUserNamePhone", null);
                threeDetailedParamMap.put("downloadDate", null);
            } else {
                threeDetailedParamMap.put("unitName", unitName);
                threeDetailedParamMap.put("fillUserNamePhone", userName + "-" + phone);
                threeDetailedParamMap.put("downloadDate", DateUtil.format(new Date(), "yyyy年MM月dd日"));
            }

            List<Map<String, Object>> dataList = Lists.newArrayList();
            int num = 0;
            for (Sqfw_formSummary formSummary : formSummaryList) {
                Map<String, Object> rollDataMap = Maps.newHashMap();
                if ("临摊、夜市摊".equals(formSummary.getA61()) && formSummary.getA17() != null &&
                        "瓶装液化气/管道燃气/用电".contains(formSummary.getA17())) {

                    //zhangp 2023-12-25 只要检测到有一条数据就返回
                    if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                        return "true";
                    }

                    num++;
                    rollDataMap.put("num", num);
                    rollDataMap.put("a1", formSummary.getA1());
                    rollDataMap.put("a2", formSummary.getA2());
                    rollDataMap.put("a25", formSummary.getA25());
                    rollDataMap.put("a26_a27", "使用" + formSummary.getA26() + "瓶、存储" + formSummary.getA27() + "瓶");
                    if (("管道燃气-用电".contains(formSummary.getA17()))) {
                        rollDataMap.put("a26_a27", "");
                    }
                    rollDataMap.put("a5", formSummary.getA5());
                    rollDataMap.put("a29", formSummary.getA29());
                    rollDataMap.put("a28", formSummary.getA28());
                    rollDataMap.put("a17", formSummary.getA17());
                    rollDataMap.put("a16", formSummary.getA16());
                    String a38 = getA38(formSummary, Arrays.asList(0, 1, 2, 3, 4, 5));
                    rollDataMap.put("a36", "无".equals(a38) ? "否" : "是");
                    int changeNum = getNotChange(formSummaryList, formSummary);
                    rollDataMap.put("a37", "无".equals(a38) ? "" : changeNum == 0 ? "是" : "否");
                    rollDataMap.put("a3", formSummary.getA3());
                    rollDataMap.put("a4", formSummary.getA4());
                    dataList.add(rollDataMap);

                }
            }
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                if (dataList.isEmpty()) {
                    return "false";
                }
            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("weiQingMen/gasUseAndStorage.xlsx");
            //String fileName = "夜市摊点、游摊游贩燃气使用、存储情况台账表";
            String fileName = DownLoadFileNameEnum.WQL_GAS_USAGE_AND_STORAGE.getName();

            if (StringUtils.isNotBlank(excelParamDTO.getFileNameSuffix()))
                fileName += excelParamDTO.getFileNameSuffix();

            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(threeDetailedParamMap);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(String.class);
            excelFileCreateParam.setFileName(fileName);
            excelFileCreateParam.setReturnWebUrlFlag(excelParamDTO.getReturnWebUrlFlag());

            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        }

        // 城镇燃气餐饮用户台账表
        if (fileIndex == 5) {

            if (excelParamDTO.getIsTemplate()) {
                threeDetailedParamMap.put("unitName", null);
                threeDetailedParamMap.put("fillUserNamePhone", null);
                threeDetailedParamMap.put("downloadDate", null);
            } else {
                threeDetailedParamMap.put("unitName", unitName);
                threeDetailedParamMap.put("fillUserNamePhone", userName + "-" + phone);
                threeDetailedParamMap.put("downloadDate", DateUtil.format(new Date(), "yyyy年MM月dd日"));
            }

            List<Map<String, Object>> dataList = Lists.newArrayList();
            int num = 0;
            for (Sqfw_formSummary formSummary : formSummaryList) {
                Map<String, Object> rollDataMap = Maps.newHashMap();
                if ("餐饮店面".equals(formSummary.getA61()) && formSummary.getA17() != null &&
                        "瓶装液化气/管道燃气/用电".contains(formSummary.getA17())) {

                    //zhangp 2023-12-25 只要检测到有一条数据就返回
                    if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                        return "true";
                    }

                    num++;
                    rollDataMap.put("num", num);
                    rollDataMap.put("a1", formSummary.getA1());
                    rollDataMap.put("a2", formSummary.getA2());
                    rollDataMap.put("a23", formSummary.getA23());
                    rollDataMap.put("a26_a27", "使用" + formSummary.getA26() + "瓶、存储" + formSummary.getA27() + "瓶");
                    if (("管道燃气-用电".contains(formSummary.getA17()))) {
                        rollDataMap.put("a26_a27", "");
                    }
                    rollDataMap.put("a5", formSummary.getA5());
                    rollDataMap.put("a29", formSummary.getA29());
                    rollDataMap.put("a28", formSummary.getA28());
                    rollDataMap.put("a17", formSummary.getA17());
                    rollDataMap.put("a16", formSummary.getA16());
                    String a38 = getA38(formSummary, Arrays.asList(0, 1, 2, 3, 4, 5));
                    rollDataMap.put("a36", "无".equals(a38) ? "否" : "是");
                    int changeNum = getNotChange(formSummaryList, formSummary);
                    rollDataMap.put("a37", "无".equals(a38) ? "" : changeNum == 0 ? "是" : "否");
                    rollDataMap.put("a3", formSummary.getA3());
                    rollDataMap.put("a4", formSummary.getA4());
                    dataList.add(rollDataMap);

                }
            }
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                if (dataList.isEmpty()) {
                    return "false";
                }
            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("weiQingMen/urbanGasCatering.xlsx");
            //String fileName = "城镇燃气餐饮用户台账表";
            String fileName = DownLoadFileNameEnum.WQL_GAS_CATERING_USERS.getName();

            if (StringUtils.isNotBlank(excelParamDTO.getFileNameSuffix()))
                fileName += excelParamDTO.getFileNameSuffix();

            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(threeDetailedParamMap);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(String.class);
            excelFileCreateParam.setFileName(fileName);
            excelFileCreateParam.setReturnWebUrlFlag(excelParamDTO.getReturnWebUrlFlag());

            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        }

        // 消防安全重点单位台账
        if (fileIndex == 6) {
            List<Map<String, Object>> dataList = Lists.newArrayList();
            int num = 0;
            for (Sqfw_formSummary formSummary : formSummaryList) {
                Map<String, Object> rollDataMap = Maps.newHashMap();
                if (formSummary.getA61() != null && formSummary.getA61().contains("重点场所")) {
                    num++;

                    //zhangp 2023-12-25 只要检测到有一条数据就返回
                    if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                        return "true";
                    }

                    rollDataMap.put("num", num);
                    rollDataMap.put("county", formSummary.getCounty());
                    rollDataMap.put("a1", formSummary.getA1());
                    rollDataMap.put("a2", formSummary.getA2());
                    rollDataMap.put("a62", formSummary.getA62());
                    rollDataMap.put("a48", formSummary.getA48());
                    rollDataMap.put("a3", formSummary.getA3());
                    rollDataMap.put("a4", formSummary.getA4());
                    rollDataMap.put("a50", formSummary.getA50());
                    rollDataMap.put("a51", formSummary.getA51());
                    rollDataMap.put("a52", formSummary.getA52());
                    rollDataMap.put("a53", formSummary.getA53());
                    rollDataMap.put("a54", formSummary.getA54());
                    rollDataMap.put("a55", formSummary.getA55());
                    rollDataMap.put("a56", formSummary.getA56());
                    dataList.add(rollDataMap);

                }
            }
            if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                if (dataList.isEmpty()) {
                    return "false";
                }
            }
            String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("weiQingMen/keyUnitOfFireSafety.xlsx");
            //String fileName = "消防安全重点单位台账表";
            String fileName = DownLoadFileNameEnum.WQL_KEY_UNIT_OF_FIRE_SAFETY.getName();

            if (StringUtils.isNotBlank(excelParamDTO.getFileNameSuffix()))
                fileName += excelParamDTO.getFileNameSuffix();

            ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
            excelFileCreateParam.setTemplatePath(templatePath);
            excelFileCreateParam.setSheetParam(threeDetailedParamMap);
            excelFileCreateParam.setDataList(dataList);
            excelFileCreateParam.setClazz(String.class);
            excelFileCreateParam.setFileName(fileName);
            excelFileCreateParam.setReturnWebUrlFlag(excelParamDTO.getReturnWebUrlFlag());

            return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
        }

        return null;
    }

    /**
     * 灾防控排查整治进度统计表
     *
     * @param formSummaryList
     * @return
     */
    public static String createImprovementProgressStatistics(List<Sqfw_formSummary> formSummaryList, List<Sqfw_cenSusPaper> paramDataList, String isCheckData) {
        Map<String, List<Sqfw_formSummary>> map = new HashMap<>();
        for (Sqfw_formSummary formSummary : formSummaryList) {
            if (StringUtils.isBlank(formSummary.getA1())) continue;
            List<Sqfw_formSummary> sfs = map.get(formSummary.getCommunity());
            if (sfs == null) sfs = new ArrayList<>();
            sfs.add(formSummary);
            map.put(formSummary.getCommunity(), sfs);
        }

        Map<String, Object> threeDetailedParamMap = Maps.newHashMap();

        List<Map<String, Object>> dataList = Lists.newArrayList();
        for (String key : map.keySet()) {
            String community = key;
            int peopleNum = 0;//检查人数
            int patrolNum = 0;//检查的店面数
            int findNum = 0;//发现隐患数
            int changeNum = 0;//整改隐患数
            int violateNum = 0;//违规住人店面数

            int abnormalNum = 0;//非正常人数
            int doorNum = 0;//敲门行动户数
            int notifyNum = 0;//发放告知数
            int educationNum = 0;//观看教育数
            int propagateNum = 0;//宣传培训数

            Map<String, Object> mapData = new HashMap<>();
            List<Sqfw_formSummary> sfs = getFormSummaryList(map.get(key));
            for (Sqfw_formSummary sf : sfs) {

                //zhangp 2023-12-25 只要检测到有一条数据就返回
                if (StringUtils.isNotBlank(isCheckData) && isCheckData.equals("isCheckData")) {
                    return "true";
                }

                String peoples[] = sf.getA19().split(",");
                peopleNum += peoples.length;

                patrolNum++;

                String a37 = sf.getA37();
                if ("已整改".equals(a37) || "未整改".equals(a37)) {
                    findNum++;
                }

                if ("已整改".equals(a37)) {
                    changeNum++;
                }

                String a64 = sf.getA64();
                if ("已整改".equals(a64) || "未整改".equals(a64)) {
                    violateNum++;
                }
            }
            mapData.put("community", community);
            mapData.put("peopleNum", peopleNum);
            mapData.put("patrolNum", patrolNum);
            mapData.put("findNum", findNum);
            mapData.put("changeNum", changeNum);
            mapData.put("violateNum", violateNum);

            Set<String> set = new HashSet<>();
            Set<String> set1 = new HashSet<>();
            for (Sqfw_cenSusPaper paper : paramDataList) {
                if (community.equals(paper.getCommunity())) {
                    if (!"正常".equals(paper.getSpecialMark())) {
                        abnormalNum++;
                    }
                    set.add(paper.getDetailAddress() + paper.getBuilding() + paper.getHouseNumber());
                    if ("入户成功".equals(paper.getSurvey())) {
                        set1.add(paper.getDetailAddress() + paper.getBuilding() + paper.getHouseNumber());
                    }
                }
            }
            mapData.put("abnormalNum", abnormalNum);
            doorNum = set.size();
            notifyNum = set1.size();
            educationNum = set1.size();
            propagateNum = set1.size();
            mapData.put("doorNum", doorNum);
            mapData.put("notifyNum", notifyNum);
            mapData.put("educationNum", educationNum);
            mapData.put("propagateNum", propagateNum);

            mapData.put("other", null);//其他没有数据的列
            dataList.add(mapData);
        }
        //zhangp 2023-12-22 数据检测时使用
        if (StringUtils.isNotBlank(isCheckData) && "isCheckData".equals(isCheckData)) {
            if (dataList.isEmpty()) {
                return "false";
            }
        }
        String templatePath = CommonFormOfficeUtils.getTemplatePathByOS("weiQingMen/improvementProgressStatistics.xlsx");
        //String fileName = "灾防控排查整治进度统计";
        String fileName = DownLoadFileNameEnum.WQL_IMPROVEMENT_PROGRESS_STATISTICS.getName();

        ExcelFileCreateParamBO excelFileCreateParam = new ExcelFileCreateParamBO();
        excelFileCreateParam.setTemplatePath(templatePath);
        excelFileCreateParam.setSheetParam(threeDetailedParamMap);
        excelFileCreateParam.setDataList(dataList);
        excelFileCreateParam.setClazz(String.class);
        excelFileCreateParam.setFileName(fileName);
        excelFileCreateParam.setReturnWebUrlFlag(false);

        return CommonFormOfficeUtils.createExcelAndUpload(excelFileCreateParam);
    }

    /**
     * 按a1分组去重，并获取最新的一条
     *
     * @param formSummaryList
     * @return
     */
    private static List<Sqfw_formSummary> getFormSummaryList(List<Sqfw_formSummary> formSummaryList) {
        List<Sqfw_formSummary> newList = new ArrayList<>();
        Map<String, List<Sqfw_formSummary>> map =
                //按照createDate排序 reversed()是反转,升序变为降序,也就是说把最新的数据放到list首
                formSummaryList.stream().sorted(Comparator.comparing(Sqfw_formSummary::getCreateDate).reversed())
                        // 正常是用：：符号就可以 但是我这个对象比较特殊有三层的属性所以这么写了
                        .collect(Collectors.groupingBy(formSummary -> formSummary.getA1()));
        for (Map.Entry<String, List<Sqfw_formSummary>> entry : map.entrySet()) {
            newList.add(entry.getValue().get(0));
        }
        return newList;
    }

    public static String getA38(Sqfw_formSummary formSummary, List<Integer> nums) {
        StringBuffer stringBuffer = new StringBuffer();
        JSONArray jsonArray = null;
        try {
            jsonArray = JSON.parseArray(formSummary.getA38());
        } catch (Exception e) {
            return "无";
        }
        JSONArray a38 = new JSONArray();
        if (nums.contains(0)) a38.addAll(jsonArray.getJSONArray(0));
        if (nums.contains(1)) a38.addAll(jsonArray.getJSONArray(1));
        if (nums.contains(2)) a38.addAll(jsonArray.getJSONArray(2));
        if (jsonArray.size() > 3) {
            if (nums.contains(3)) a38.addAll(jsonArray.getJSONArray(3));
            if (nums.contains(4)) a38.addAll(jsonArray.getJSONArray(4));
            if (nums.contains(5)) a38.addAll(jsonArray.getJSONArray(5));
        }
        int i = 0;
        for (Object o : a38) {
            if (!"无".equals(o.toString())) {
                i++;
                stringBuffer.append(i + "、");
                String s = o.toString();
                //去掉序号
                stringBuffer.append(s.substring(s.indexOf(".") + 1));
            }
        }
        return stringBuffer.length() == 0 ? "无" : stringBuffer.toString();
    }

    private static String getA38_A31(Sqfw_formSummary formSummary, List<Integer> nums) {
        StringBuffer stringBuffer = new StringBuffer();
        JSONArray jsonArray = null;
        try {
            jsonArray = JSON.parseArray(formSummary.getA38());
        } catch (Exception e) {
            return "无";
        }
        JSONArray a38 = new JSONArray();
        if (nums.contains(0)) a38.addAll(jsonArray.getJSONArray(0));
        if (nums.contains(1)) a38.addAll(jsonArray.getJSONArray(1));
        if (nums.contains(2)) a38.addAll(jsonArray.getJSONArray(2));
        if (jsonArray.size() > 3) {
            if (nums.contains(3)) a38.addAll(jsonArray.getJSONArray(3));
            if (nums.contains(4)) a38.addAll(jsonArray.getJSONArray(4));
            if (nums.contains(5)) a38.addAll(jsonArray.getJSONArray(5));
        }
        if (!StrUtil.isBlank(formSummary.getA31())) a38.add(formSummary.getA31());
        int i = 0;
        for (Object o : a38) {
            if (!"无".equals(o.toString())) {
                i++;
                stringBuffer.append(i + "、");
                String s = o.toString();
                //去掉序号
                stringBuffer.append(s.substring(s.indexOf(".") + 1));
            }
        }
        return stringBuffer.length() == 0 ? "无" : stringBuffer.toString();
    }

    private static String getA42(List<Sqfw_formSummary> formSummaryList, Sqfw_formSummary formSummary, List<Integer> nums) {
        List<Sqfw_formSummary> formSummaryListUp = new ArrayList<>();
        for (Sqfw_formSummary f : formSummaryList) {
            //zhangp 2023-12-19  报空指针增加判断
            if (StringUtils.isNotBlank(f.getA1()) && f.getA1().equals(formSummary.getA1()) && f.getFillDate().getTime() > formSummary.getFillDate().getTime() &&
                    !StrUtil.isBlank(f.getA42()) && JSON.parseArray(f.getA42()).size() > 0) {
                formSummaryListUp.add(f);
            }
        }
        if (formSummaryListUp.size() > 0) {
            Collections.sort(formSummaryListUp, Comparator.comparing(obj -> obj.getFillDate()));//升序
            //Sqfw_formSummary formSummaryUp = formSummaryListUp.get(formSummaryListUp.size() - 1);//获取最后一个元素
            Sqfw_formSummary formSummaryUp = formSummaryListUp.get(0);
            String a42 = formSummary.getA42();
            String a42Up = formSummaryUp.getA42();
            /*List<String> a42_1 = new ArrayList<>();
            List<String> a42Up_1 = new ArrayList<>();
            if (nums.contains(0)) {
                a42_1 = JSON.parseArray(JSON.parseArray(a42).getString(0), String.class);
                a42Up_1 = JSON.parseArray(JSON.parseArray(a42Up).getString(0), String.class);
            }*/
            List<String> a42_2 = new ArrayList<>();
            List<String> a42Up_2 = new ArrayList<>();
            if (nums.contains(1)) {
                a42_2 = JSON.parseArray(JSON.parseArray(a42).getString(1), String.class);
                a42Up_2 = JSON.parseArray(JSON.parseArray(a42Up).getString(1), String.class);
            }
            List<String> a42_3 = new ArrayList<>();
            List<String> a42Up_3 = new ArrayList<>();
            if (nums.contains(2)) {
                a42_3 = JSON.parseArray(JSON.parseArray(a42).getString(2), String.class);
                a42Up_3 = JSON.parseArray(JSON.parseArray(a42Up).getString(2), String.class);
            }

            if (a42Up_2.size() + a42Up_3.size() == 0) return "已完成";

            //获取交集
            //Collection<String> collection_1 = CollectionUtils.intersection(a42Up_1, a42_1);
            Collection<String> collection_2 = CollectionUtils.intersection(a42Up_2, a42_2);
            Collection<String> collection_3 = CollectionUtils.intersection(a42Up_3, a42_3);
            return collection_2.size() + collection_3.size() == 0 ? "已完成" : "定期检查";
        }
        String a42 = formSummary.getA42();
        List<String> a42_2 = new ArrayList<>();
        if (nums.contains(1)) {
            a42_2 = JSON.parseArray(JSON.parseArray(a42).getString(1), String.class);
        }
        List<String> a42_3 = new ArrayList<>();
        if (nums.contains(2)) {
            a42_3 = JSON.parseArray(JSON.parseArray(a42).getString(2), String.class);
        }
        if (a42_2.size() + a42_3.size() == 0) return "已完成";
        return "定期检查";
    }

    /**
     * 获取未整改数
     *
     * @param formSummaryList
     * @param formSummary
     * @return
     */
    private static int getNotChange(List<Sqfw_formSummary> formSummaryList, Sqfw_formSummary formSummary) {
        List<Sqfw_formSummary> formSummaryListUp = new ArrayList<>();
        for (Sqfw_formSummary f : formSummaryList) {
            //zhangp 2023-12-14 报空指针增加a1判断
            if (null != f.getA1() && f.getA1().equals(formSummary.getA1()) && f.getFillDate().getTime() > formSummary.getFillDate().getTime()) {
                formSummaryListUp.add(f);
            }
        }
        if (formSummaryListUp.size() > 0) {
            Collections.sort(formSummaryListUp, Comparator.comparing(obj -> obj.getFillDate()));//升序
            //Sqfw_formSummary formSummaryUp = formSummaryListUp.get(formSummaryListUp.size() - 1);//获取最后一个元素
            Sqfw_formSummary formSummaryUp = formSummaryListUp.get(formSummaryListUp.size() - 1);
            String a42 = formSummary.getA42();
            String a42Up = formSummaryUp.getA42();
            //一般隐患
            List<String> a42_2 = JSON.parseArray(JSON.parseArray(a42).getString(1), String.class);
            //重大隐患
            List<String> a42_3 = JSON.parseArray(JSON.parseArray(a42).getString(2), String.class);

            List<String> a42Up_2 = JSON.parseArray(JSON.parseArray(a42Up).getString(1), String.class);
            List<String> a42Up_3 = JSON.parseArray(JSON.parseArray(a42Up).getString(2), String.class);

            //获取并集，等于0说明整改完成，否则未整改完成
            Collection<String> collection_1 = CollectionUtils.intersection(a42_2, a42Up_2);
            Collection<String> collection_2 = CollectionUtils.intersection(a42_3, a42Up_3);
            return collection_1.size() + collection_2.size();
        }
        String a42 = formSummary.getA42();
        //一般隐患
        List<String> a42_2 = JSON.parseArray(JSON.parseArray(a42).getString(1), String.class);
        //重大隐患
        List<String> a42_3 = JSON.parseArray(JSON.parseArray(a42).getString(2), String.class);
        return a42_2.size() + a42_3.size();
    }

    /**
     * 获取整改数
     *
     * @param formSummaryList
     * @param formSummary
     * @return
     */
    private static int[] getChange(List<Sqfw_formSummary> formSummaryList, Sqfw_formSummary formSummary) {
        List<Sqfw_formSummary> formSummaryListUp = new ArrayList<>();
        for (Sqfw_formSummary f : formSummaryList) {
            if (StringUtils.isNotBlank(f.getA1()) && f.getA1().equals(formSummary.getA1()) && f.getFillDate().getTime() > formSummary.getFillDate().getTime()) {
                formSummaryListUp.add(f);
            }
        }
        if (formSummaryListUp.size() > 0) {
            Collections.sort(formSummaryListUp, Comparator.comparing(obj -> obj.getFillDate()));//升序
            //Sqfw_formSummary formSummaryUp = formSummaryListUp.get(formSummaryListUp.size() - 1);//获取最后一个元素
            Sqfw_formSummary formSummaryUp = formSummaryListUp.get(formSummaryListUp.size() - 1);
            String a42 = formSummary.getA42();
            String a42Up = formSummaryUp.getA42();
            //一般隐患
            List<String> a42_2 = JSON.parseArray(JSON.parseArray(a42).getString(1), String.class);
            //重大隐患
            List<String> a42_3 = JSON.parseArray(JSON.parseArray(a42).getString(2), String.class);

            List<String> a42Up_2 = JSON.parseArray(JSON.parseArray(a42Up).getString(1), String.class);
            List<String> a42Up_3 = JSON.parseArray(JSON.parseArray(a42Up).getString(2), String.class);

            //获取差集
            Collection<String> collection1 = CollectionUtils.subtract(a42_2, a42Up_2);//一般隐患条数
            Collection<String> collection2 = CollectionUtils.subtract(a42_3, a42Up_3);//重大隐患条数
            return new int[]{collection1.size(), collection2.size()};
        }
        return new int[]{0, 0};
    }
}
